﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace SSCCMembership.Web.Services.Members
{
    [ErrorHandlerBehavior]
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class QualificationService
    {
        readonly QualificationCategory[] QCats = new QualificationCategory[] 
        { 
            new QualificationCategory(7, "Paddling Qualifications (eg Paddlepower & Star Awards)", false) ,
            new QualificationCategory(8, "Coaching Qualifications", true),
            new QualificationCategory(9, "Other Qualifications (including Safety, First Aid and Child Protections)", true)
        };
        class QualificationCategory
        {
            public QualificationCategory(int CodeType, string Title, bool HasExpiryDate)
            {
                this.CodeType = CodeType;
                this.Title = Title;
                this.HasExpiryDate = HasExpiryDate;
            }
            public int CodeType { get; private set; }
            public string Title { get; private set; }
            public bool HasExpiryDate { get; private set; }
        }

        [OperationContract]
        public QualificationsData[] GetQualifications(int MemberID)
        {
            // Add your operation implementation here
            return QCats.Select((QC) =>
                {
                    QualificationsData QD = new QualificationsData()
                    {
                        CodeType = QC.CodeType,
                        HasExpiryDate = QC.HasExpiryDate,
                        Title = QC.Title
                        //MemberID=MemberID
                    };
                    using (var Q = SSCCDatabase.Query("SELECT ID, CategoryCT, QTypeRC, QLevelRCL2, YearAttained, RenewalDate, Comment FROM SSCC_DAT_Qualification WHERE Member=@ID AND CategoryCT=@CT",
                        new Dictionary<SqlParameter, object>(){
                        {new SqlParameter("@ID", SqlDbType.Int), MemberID},
                        {new SqlParameter("@CT", SqlDbType.Int), QC.CodeType}
                        }))
                    {
                        QD.Qualifications = Q.Select(SelectQualification).ToArray();
                    }
                    return QD;
                }).ToArray();
            //return QCats.Select((QC) => 
            //{
            //    QualificationsData QD = new QualificationsData()
            //    {
            //        CodeType = QC.CodeType,
            //        HasExpiryDate = QC.HasExpiryDate,
            //        Title = QC.Title
            //        //MemberID=MemberID
            //    };
            //    QD.Qualifications = DC.SSCC_DAT_Qualification.Where((Q) => Q.SSCC_DAT_Member.ID == MemberID && Q.CategoryCT == QD.CodeType).Select(SelectQualification).ToArray();
            //    return QD;
            //}).ToArray();
        }
        [OperationContract]
        public int New(int MemberID, int CodeType)
        {
            if (SSCCDatabase.ExecuteStatement("INSERT INTO SSCC_DAT_Qualification (CategoryCT, Member) Values (@CT, @MemberID)", new Params(){
            {new SqlParameter("@CT", SqlDbType.Int), CodeType},
            {new SqlParameter("@MemberID", SqlDbType.Int), MemberID}
            }) == 1)
            {
                return SSCCDatabase.HighestID("SSCC_DAT_Qualification");
            }
            throw new Exception("Failed to add new Qualification");
        }
        [OperationContract]
        public void UpdateProperty(int ID, string PropertyName, object PropertyValue)
        {
            SqlParameter P = new SqlParameter();
            switch (PropertyName)
            {
                case "CategoryCT":
                case "QTypeRC":
                case "QLevelRCL2":
                case "YearAttained":
                    P = new SqlParameter("@value", SqlDbType.Int);
                    break;
                case "RenewalDate":
                    P = new SqlParameter("@value", SqlDbType.Date);
                    break;
                case "Comment":
                    P = new SqlParameter("@value", SqlDbType.VarChar);
                    break;
                default:
                    return;
            }
            SSCCDatabase.ExecuteStatement("UPDATE SSCC_DAT_Qualification SET " + PropertyName + "=@value WHERE ID=@ID"
                , new Dictionary<SqlParameter, object>()
                {
                    {P, PropertyValue.ToDBNullable()},
                    {new SqlParameter("@ID", SqlDbType.Int), ID}
                });
        }

        [OperationContract]
        public void Delete(int ID)
        {
            SSCCDatabase.ExecuteStatement("DELETE FROM SSCC_DAT_Qualification WHERE ID=@ID", new SqlParameter("@ID", SqlDbType.Int), ID);
        }


        
        // Add more operations here and mark them with [OperationContract]

        QualificationData SelectQualification(SqlDataReader Q)
        {
            var QD = new QualificationData();
            QD.ID = (int)Q["ID"];
            QD.CategoryCT = (int)Q["CategoryCT"];
            QD.HasExpiryDate = QCats.First((QC) => QC.CodeType == QD.CategoryCT).HasExpiryDate;
            QD.QTypeRC = Q["QTypeRC"].Nullable<int?>();
            QD.QLevelRCL2 = Q["QLevelRCL2"].Nullable<int?>();
            QD.YearAttained = Q["YearAttained"].Nullable<int?>();
            QD.RenewalDate = Q["RenewalDate"].Nullable<DateTime?>();
            QD.Comment = Q["Comment"].ToString();
            return QD;
        }

        public class QualificationsData
        {
            public int CodeType { get; set; }
            public string Title { get; set; }
            public bool HasExpiryDate { get; set; }
            //public int MemberID { get; set; }

            public QualificationData[] Qualifications{get; set;}
        }
        public class QualificationData
        {
            public int ID { get; set; }
            public int CategoryCT { get; set; }
            public bool HasExpiryDate { get; set; }
            public int? QTypeRC { get; set; }
            public int? QLevelRCL2 { get; set; }
            public int? YearAttained { get; set; }
            public DateTime? RenewalDate { get; set; }
            public string Comment { get; set; }
        }
    }
}
